package web.java.f_DbUtils;

import static org.junit.Assert.*;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import web.java.d_test.Emp;
import web.java.z_Utils.DBUtils;

/**
 * 0) ResultSetHandler: 自定义工具
 * 1) BeanHandler: 查询返回单个对象
 * 2) BeanListHandler: 查询返回list集合，集合元素是指定的对象
 * 3) ArrayHandler, 查询返回结果记录的第一行，封装对对象数组, 即返回：Object[]
 * 4) ArrayListHandler, 把查询的每一行都封装为对象数组，再添加到list集合中
 * */

public class Demo03_query {
	@Test
	public void testQuery() throws Exception {
		String sql="select * from emp where emp_id=?";
		Connection conn=DBUtils.getConnection();
		QueryRunner runner=new QueryRunner();
		Emp emp=runner.query(conn,sql,new ResultSetHandler<Emp>(){
			@Override
			public Emp handle(ResultSet result) throws SQLException {
				// TODO Auto-generated method stub
				if(result.next()){
					Emp emp=new Emp();
					emp.setEmp_age(result.getInt("emp_age"));
					emp.setEmp_consume(result.getInt("emp_consume"));
					emp.setEmp_gender(result.getString("emp_gender"));
					emp.setEmp_id(result.getInt("emp_id"));
					emp.setEmp_name(result.getString("emp_name"));
					emp.setEmp_salary(result.getInt("emp_salary"));
					return emp;
				}
				return null;
			}},1);
		System.out.println(emp);
		DbUtils.close(conn);
	}
	@Test
	public void testBeanHandler() throws Exception {
		String sql="select * from emp where emp_id=?";
		Connection conn=DBUtils.getConnection();
		QueryRunner runner=new QueryRunner();
		Emp emp=runner.query(conn,sql,new BeanHandler<Emp>(Emp.class),2);
		System.out.println(emp);
		conn.close();
	}
	@Test
	public void testBeanListHandler() throws Exception {
		String sql="select * from emp";
		Connection conn=DBUtils.getConnection();
		QueryRunner runner=new QueryRunner();
		List<Emp> list=runner.query(conn,sql,new BeanListHandler<Emp>(Emp.class));
		for(Emp emp:list){
			System.out.println(emp);
		}
		conn.close();
	}
	@Test
	public void testArrayHandler() throws Exception {
		String sql="select * from emp";
		Connection conn=DBUtils.getConnection();
		QueryRunner runner=new QueryRunner();
		Object[] obj=runner.query(conn,sql,new ArrayHandler());
		System.out.println(obj);
		for(int i=0;i<obj.length;i++){
			System.out.print(obj[i]+"\t");
		}
		conn.close();
	}
	@Test
	public void testArrayListHandler() throws Exception {
		String sql="select * from emp";
		Connection conn=DBUtils.getConnection();
		QueryRunner runner=new QueryRunner();
		List<Object[]> list=runner.query(conn,sql,new ArrayListHandler());
		for(Object[] obj:list){
			for(int i=0;i<obj.length;i++){
				System.out.print(obj[i]+"\t");
			}
			System.out.println();
		}
		conn.close();
	}
	@Test
	public void testScalarHandler() throws Exception {
		String sql="select sum(emp_id) from emp";
		Connection conn=DBUtils.getConnection();
		QueryRunner runner=new QueryRunner();
		BigDecimal result=runner.query(conn,sql,new ScalarHandler<BigDecimal>());
		System.out.println(result);
		conn.close();
	}
	@Test
	public void testMapHandler() throws Exception {
		String sql="select * from emp where emp_id=?";
		Connection conn=DBUtils.getConnection();
		QueryRunner runner=new QueryRunner();
		Map<String,Object> map=runner.query(conn,sql,new MapHandler(),10);
		Set<Entry<String,Object>> entrys=map.entrySet();
		for(Entry<String,Object> entry:entrys){
			System.out.println(entry.getKey()+":"+entry.getValue());
		}
		conn.close();
	}
	@Test
	public void testMapListHandler() throws Exception {
		String sql="select * from emp";
		Connection conn=DBUtils.getConnection();
		QueryRunner runner=new QueryRunner();
		List<Map<String,Object>> list=runner.query(conn,sql,new MapListHandler());
		for(Map<String,Object> map:list){
			for(Entry<String,Object> entry:map.entrySet()){
				System.out.print(entry.getKey()+":"+entry.getValue()+"\t");
			}
			System.out.println();
		}
	}
}
